from flask import Flask, request, jsonify
import pymysql
from pymysql.cursors import DictCursor
import json
from datetime import datetime, date
import logging

app = Flask(__name__)

# 配置日志
logging.basicConfig(level=logging.DEBUG)
logger = logging.getLogger(__name__)

# 数据库连接配置
DB_CONFIG = {
    "host": "localhost",
    "port": 3306,
    "user": "root",
    "password": "syy741255",
    "db": "test",
    "charset": "utf8",
    "cursorclass": DictCursor
}


def get_db_connection():
    """获取数据库连接"""
    return pymysql.connect(**DB_CONFIG)


# 把特殊格式转化
class CustomJSON(json.JSONEncoder):
    def default(self, obj):
        if isinstance(obj, (datetime, date)):
            return obj.isoformat()
        return super().default(obj)


def execute_query(cur, query, params=None):
    """执行SQL查询并返回结果"""
    try:
        logger.debug(f"Executing query: {query} with params: {params}")
        cur.execute(query, params)
        return cur.fetchall()
    except Exception as e:
        logger.error(f"Error executing query: {e}")
        raise e


@app.route('/homeinfo', methods=["GET"])
def homeinfo():
    try:
        with get_db_connection() as conn:
            with conn.cursor() as cur:
                result = execute_query(cur, "SELECT * FROM homeinfo ORDER BY contentType")
                data = json.dumps(result, indent=4)
                return jsonify(data)
    except Exception as e:
        return jsonify({"error": str(e)}), 500


@app.route('/about', methods=["GET"])  # 轮播图
def about():
    try:
        with get_db_connection() as conn:
            with conn.cursor() as cur:
                res = execute_query(cur, "SELECT * FROM aboutus")
                data = json.dumps(res, indent=4)
                return jsonify(data)
    except Exception as e:
        return jsonify({"error": str(e)}), 500


@app.route('/orders', methods=["GET"])  # 提供服务
def orders():
    try:
        with get_db_connection() as conn:
            with conn.cursor() as cur:
                res1 = execute_query(cur,
                                     "SELECT * FROM orders WHERE recomflag = 1 AND supplyflag = 0 AND delflag = 1 ORDER BY publishtime DESC LIMIT 1, 4")
                res2 = execute_query(cur,
                                     "SELECT * FROM orders WHERE recomflag = 1 AND supplyflag = 1 AND delflag = 1 ORDER BY publishtime DESC LIMIT 1, 4")
                data = {"purchase": res1, "supply": res2}
                data = json.dumps(data, cls=CustomJSON)
                return jsonify(data)
    except Exception as e:
        return jsonify({"error": str(e)}), 500


@app.route('/com', methods=["GET"])  # 合作公司
def com():
    try:
        with get_db_connection() as conn:
            with conn.cursor() as cur:
                res1 = execute_query(cur,
                                     "SELECT * FROM company WHERE deleteflag=1 AND recommend=1 AND category=1 ORDER BY registerTime DESC")
                res2 = execute_query(cur,
                                     "SELECT * FROM company WHERE deleteflag=1 AND recommend=1 AND category=2 ORDER BY registerTime DESC")
                res3 = execute_query(cur,
                                     "SELECT * FROM company WHERE deleteflag=1 AND recommend=1 AND category=3 ORDER BY registerTime DESC")
                data = {"type1": res1, "type2": res2, "type3": res3}
                data = json.dumps(data, cls=CustomJSON)
                return jsonify(data)
    except Exception as e:
        return jsonify({"error": str(e)}), 500


@app.route('/business', methods=["GET"])  # 行业分类
def business():
    try:
        with get_db_connection() as conn:
            with conn.cursor() as cur:
                res = execute_query(cur, "SELECT * FROM business")
                data = json.dumps(res, indent=4)
                return jsonify(data)
    except Exception as e:
        return jsonify({"error": str(e)}), 500


@app.route('/sidebar1', methods=["GET"])  # 需求供应侧边栏
def sidebar1():
    try:
        with get_db_connection() as conn:
            with conn.cursor() as cur:
                res = execute_query(cur,
                                    "SELECT * FROM orders WHERE delflag=1 AND supplyflag=1 AND recomflag=1 ORDER BY publishtime DESC LIMIT 1, 8")
                data = json.dumps(res, cls=CustomJSON)
                return jsonify(data)
    except Exception as e:
        return jsonify({"error": str(e)}), 500


@app.route('/purchase', methods=["GET"])  # 需求信息
def purchase():
    try:
        bid = request.args.get("businessid", type=int)  # 行业分类编号
        page = request.args.get("page", 1, type=int)  # 当前页数，默认为1
        page_size = request.args.get("page_size", 10, type=int)  # 查询条数，默认为10
        offset = (page - 1) * page_size  # 偏移量

        count_query = "SELECT COUNT(*) AS total FROM orders WHERE delflag = 1 AND supplyflag = 0"
        query = "SELECT * FROM orders WHERE delflag = 1 AND supplyflag = 0"

        if bid is not None:
            count_query += f" AND businessid = %s"
            query += f" AND businessid = %s"

        params = [bid] if bid is not None else []

        with get_db_connection() as conn:
            with conn.cursor() as cur:
                total = execute_query(cur, count_query, params)[0]
                total_records = total["total"] if total else 0  # 总个数
                total_pages = (total_records + page_size - 1) // page_size  # 计算总页数

                query += f" ORDER BY publishtime DESC LIMIT %s OFFSET %s"
                params.extend([page_size, offset])

                res = execute_query(cur, query, params)
                result = {
                    "data": res,
                    "page": page,
                    "page_size": page_size,
                    "total_records": total_records,
                    "total_pages": total_pages
                }
                data = json.dumps(result, cls=CustomJSON)
                return jsonify(data)
    except Exception as e:
        return jsonify({"error": str(e)}), 500


@app.route('/sidebar2', methods=["GET"])  # 采购侧边栏
def sidebar2():
    try:
        with get_db_connection() as conn:
            with conn.cursor() as cur:
                res = execute_query(cur,
                                    "SELECT * FROM orders WHERE delflag=1 AND supplyflag=0 AND recomflag=1 ORDER BY publishtime DESC LIMIT 1, 8")
                data = json.dumps(res, cls=CustomJSON)
                return jsonify(data)
    except Exception as e:
        return jsonify({"error": str(e)}), 500


@app.route('/supply', methods=["GET"])  # 需求信息
def supply():
    try:
        bid = request.args.get("businessid", type=int)  # 行业分类编号
        page = request.args.get("page", 1, type=int)  # 当前页数，默认为1
        page_size = request.args.get("page_size", 10, type=int)  # 查询条数，默认为10
        offset = (page - 1) * page_size  # 偏移量

        count_query = "SELECT COUNT(*) AS total FROM orders WHERE delflag = 1 AND supplyflag = 1"
        query = "SELECT * FROM orders WHERE delflag = 1 AND supplyflag = 1"

        if bid is not None:
            count_query += f" AND businessid = %s"
            query += f" AND businessid = %s"

        params = [bid] if bid is not None else []

        with get_db_connection() as conn:
            with conn.cursor() as cur:
                total = execute_query(cur, count_query, params)[0]
                total_records = total["total"] if total else 0  # 总个数
                total_pages = (total_records + page_size - 1) // page_size  # 计算总页数

                query += f" ORDER BY publishtime DESC LIMIT %s OFFSET %s"
                params.extend([page_size, offset])

                res = execute_query(cur, query, params)
                result = {
                    "data": res,
                    "page": page,
                    "page_size": page_size,
                    "total_records": total_records,
                    "total_pages": total_pages
                }
                data = json.dumps(result, cls=CustomJSON)
                return jsonify(data)
    except Exception as e:
        return jsonify({"error": str(e)}), 500


@app.route('/orderbyid', methods=["GET"])  # 供需信息详情
def orderbyid():
    try:
        orderid = request.args.get("orderid")  # 编号
        with get_db_connection() as conn:
            with conn.cursor() as cur:
                res = execute_query(cur, "SELECT * FROM orders WHERE orderid=%s", (orderid,))
                data = json.dumps(res[0], cls=CustomJSON) if res else {}
                return jsonify(data)
    except Exception as e:
        return jsonify({"error": str(e)}), 500

@app.route('/mtype', methods=["GET"])  # 物流菜单
def mtype():
    try:
        with get_db_connection() as conn:
            with conn.cursor() as cur:
                res = execute_query(cur, "SELECT * FROM mtype ORDER BY id")
                data = json.dumps(res, indent=4)
                return jsonify(data)
    except Exception as e:
        return jsonify({"error": str(e)}), 500


@app.route('/mflow', methods=["GET"])  # 物流信息
def mflow():
    try:
        with get_db_connection() as conn:
            with conn.cursor() as cur:
                res = execute_query(cur, "SELECT * FROM mflow ORDER BY moduleid")
                data = json.dumps(res, indent=4)
                return jsonify(data)
    except Exception as e:
        return jsonify({"error": str(e)}), 500

@app.route('/assitant', methods=["GET"])  # 国际物流
def assitant():
    try:
        with get_db_connection() as conn:
            with conn.cursor() as cur:
                cur.execute("SELECT * FROM assitant ORDER BY id")
                res = cur.fetchall()
                data = json.dumps(res, cls=CustomJSON)
                return jsonify(data)
    except Exception as e:
        return jsonify({"error": str(e)}), 500


@app.route('/news', methods=["GET"])  # 新闻信息
def news():
    try:
        with get_db_connection() as conn:
            with conn.cursor() as cur:
                cur.execute("SELECT * FROM news WHERE disp=1 AND category=1 ORDER BY updatetime DESC")
                res1 = cur.fetchall()
                cur.execute("SELECT * FROM news WHERE disp=1 AND category=2 ORDER BY updatetime DESC")
                res2 = cur.fetchall()
                cur.execute("SELECT * FROM news WHERE disp=1 AND category=3 ORDER BY updatetime DESC")
                res3 = cur.fetchall()
                data = {"news1": res1, "news2": res2, "news3": res3}
                data = json.dumps(data, cls=CustomJSON)
                return jsonify(data)
    except Exception as e:
        return jsonify({"error": str(e)}), 500


@app.route('/newstype', methods=["GET"])  # 新闻类别
def newstype():
    try:
        with get_db_connection() as conn:
            with conn.cursor() as cur:
                cur.execute("SELECT * FROM newstype ORDER BY tid")
                res = cur.fetchall()
                data = json.dumps(res, cls=CustomJSON)
                return jsonify(data)
    except Exception as e:
        return jsonify({"error": str(e)}), 500

@app.route('/newsbyid', methods=["GET"])  # 新闻详情
def newsbyid():
    try:
        news_id = request.args.get("id")
        if news_id is None:
            return jsonify({"error": "ID parameter is required"}), 400

        with get_db_connection() as conn:
            with conn.cursor() as cur:
                query = f"SELECT * FROM news WHERE disp=1 AND id={news_id}"
                res = execute_query(cur, query)
                if not res:
                    return jsonify({"error": "News not found"}), 404
                data = json.dumps(res[0], cls=CustomJSON)
                return jsonify(json.loads(data))
    except Exception as e:
        logger.error(f"Error fetching news by ID: {e}")
        return jsonify({"error": str(e)}), 500



if __name__ == '__main__':
    app.run(port=5000, debug=True)



